package com.tanxi521.com.thenorthtitle.sql.dao;

import com.tanxi521.com.thenorthtitle.TheNorthTitle;
import com.tanxi521.com.thenorthtitle.entity.PlayerTitle;
import com.tanxi521.com.thenorthtitle.entity.TheNorthTitleData;
import com.tanxi521.com.thenorthtitle.entity.TheNorthTitlePlayer;
import com.tanxi521.com.thenorthtitle.sql.SqlEnum;
import com.tanxi521.tnlevel.dao.ConnectionPool;
import org.bukkit.command.CommandSender;
import org.bukkit.entity.Player;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

public class TnTitleBaseInfoMapper {

    public static void createTitleMapper() {
        PreparedStatement statement = null;
        Connection connection = null;
        String sqlTheNorthTitle = "CREATE TABLE IF NOT EXISTS `the_north_title` (" +
                "  `id` int NOT NULL AUTO_INCREMENT COMMENT '自增主键'," +
                "  `title_name` varchar(255) DEFAULT NULL COMMENT '称号名'," +
                "  `title_price` decimal(10,2) DEFAULT NULL COMMENT '称号价格'," +
                "  `title_rely_on` varchar(255) DEFAULT NULL COMMENT '前置称号'," +
                "  `title_shop_discount` decimal(10,2) DEFAULT NULL COMMENT '称号商店购买折扣'," +
                "  `title_reward_mul` decimal(10,2) DEFAULT NULL COMMENT '称号领取奖励倍率'," +
                "  `create_by` varchar(255) DEFAULT NULL," +
                "  `create_time` timestamp NULL DEFAULT NULL," +
                "  `update_by` varchar(255) DEFAULT NULL," +
                "  `update_time` timestamp NULL DEFAULT NULL," +
                "  PRIMARY KEY (`id`)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;";
        String sql = "CREATE TABLE IF NOT EXISTS `the_north_title_player` (" +
                "  `id` int NOT NULL AUTO_INCREMENT," +
                "  `player_uuid` varchar(36) DEFAULT NULL," +
                "  `player_name` varchar(50) DEFAULT NULL," +
                "  `title_id` int DEFAULT NULL," +
                "  `achieve_time` timestamp NULL DEFAULT NULL," +
                "  PRIMARY KEY (`id`)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;";
        String sqlNow = "CREATE TABLE `the_north_title_now` (" +
                "  `id` int NOT NULL AUTO_INCREMENT," +
                "  `player_name` varchar(255) DEFAULT NULL," +
                "  `player_uuid` varchar(36) DEFAULT NULL," +
                "  `player_now_title_id` varchar(255) DEFAULT NULL," +
                "  PRIMARY KEY (`id`)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3;";
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sqlTheNorthTitle);
            statement.execute();
            statement = connection.prepareStatement(sql);
            statement.execute();
            statement = connection.prepareStatement(sqlNow);
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                    ConnectionPool.returnSource(connection);
                } catch (SQLException e) {
                }
            }
        }
    }

    public static int getUserTitlesCount(String userName) {
        Connection connection = null;
        PreparedStatement statement = null;
        String sql = "select count(0) as count from the_north_title_player where player_name=?";
        ResultSet rs = null;
        int count = 0;
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, userName);
            rs = statement.executeQuery();
            rs.first();
            count = rs.getInt("count");
        } catch (SQLException e) {
            e.printStackTrace();
            TheNorthTitle.getInstance().getLogger().warning("获取用户" + userName + "称号列表出错");
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                }
            }
            ConnectionPool.returnSource(connection);
        }
        return count;
    }

    public static List<PlayerTitle> getUserTitles(int start, int pageSize, String userName) {
        Connection connection = null;
        PreparedStatement statement = null;
        List<PlayerTitle> theNorthTitlePlayers = new ArrayList<>();
        ResultSet rs = null;
        String sql = "select tnt.id," +
                "tnt.title_reward_mul" +
                "tnt.title_name, " +
                "tnt.title_price, " +
                "tnt.title_shop_discount, " +
                "(select title_name from the_north_title tnt2 where tnt2.id = tnt.title_rely_on) as title_pre," +
                "tntp.achieve_time " +
                "from the_north_title_player tntp " +
                "left join the_north_title tnt on tnt.id = tntp.title_id " +
                "where tntp.player_name = ? " +
                "order by tntp.achieve_time limit ?, ?";
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, userName);
            statement.setInt(2, start);
            statement.setInt(3, pageSize);
            rs = statement.executeQuery();
            while (rs.next()) {
                PlayerTitle playerTitle = new PlayerTitle();
                playerTitle.setTitleName(rs.getString("title_name"))
                        .setTitlePre(rs.getString("title_pre"))
                        .setTitleDisCount(rs.getString("title_shop_discount"))
                        .setPrice(rs.getString("title_price"))
                        .setAchieveTime(rs.getString("achieve_time"))
                        .setTitleRewardMul(rs.getString("title_reward_mul"))
                        .setId(rs.getInt("id"));
                theNorthTitlePlayers.add(playerTitle);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                }
            }
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
            ConnectionPool.returnSource(connection);
        }

        return theNorthTitlePlayers;

    }

    public static TheNorthTitleData getUserTitle(String playerName) {
        PreparedStatement statement = null;
        Connection connection = null;
        ResultSet rs = null;
        TheNorthTitleData titleData = new TheNorthTitleData();
        String sql = "select title_name, " +
                "title_price, " +
                "title_rely_on, " +
                "title_reward_mul, " +
                "title_shop_discount " +
                "from the_north_title_now " +
                "left join the_north_title tnt on tnt.id = player_now_title_id " +
                "where player_name = ?";
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, playerName);
            rs = statement.executeQuery();
            rs.first();
            titleData.setTitleName(rs.getString("title_name"))
                    .setTitlePrice(rs.getDouble("title_price"))
                    .setTitleRelyOn(rs.getString("title_rely_on"))
                    .setTitleRewardMul(rs.getDouble("title_reward_mul"))
                    .setTitleShopDiscount(rs.getDouble("title_shop_discount"));
        } catch (SQLException e) {
            e.printStackTrace();
            TheNorthTitle.getInstance().getLogger().warning("获取用户称号信息出错");
        } finally {
            ConnectionPool.returnSource(connection);
            try {
                if (statement != null) {
                    statement.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException ignore) {
            }
        }
        return titleData;
    }

    public static int selectHas(String userName, Integer titleId) {

        PreparedStatement statement = null;
        Connection connection = null;
        ResultSet rs = null;
        int count = 0;
        try {
            connection = ConnectionPool.getConnection();
            String sql = SqlEnum.SELECT_PLAYER_HAS.getSqlStatement();
            statement = connection.prepareStatement(sql);
            statement.setString(1, userName);
            statement.setInt(2, titleId);
            rs = statement.executeQuery();
            rs.first();
            count = rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
            TheNorthTitle.getInstance().getLogger().warning("判断是否拥有称号出错");
        } finally {
            ConnectionPool.returnSource(connection);
            try {
                if (statement != null) statement.close();
                if (rs != null) rs.close();
            } catch (SQLException e) {
            }
        }
        return count;
    }

    public static void setTitle(CommandSender sender, Integer titleId) {
        String userName = sender.getName();
        PreparedStatement statement = null;
        Connection connection = null;
        String sql = SqlEnum.SET_USER_TITLE.getSqlStatement();
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, titleId);
            statement.setString(2, userName);
            statement.execute();
            sender.sendMessage("§e[称号系统] 称号设置成功,说句话试试吧");
        } catch (SQLException e) {
            e.printStackTrace();
            sender.sendMessage("§c设置称号失败,请联系管理员");
        } finally {
            try {
                if (statement != null) statement.close();
                ConnectionPool.returnSource(connection);
            } catch (SQLException e) {
            }
        }
    }

    public static List<String> selectAll() {
        PreparedStatement statement = null;
        Connection connection = null;
        ResultSet rs = null;
        String sql = SqlEnum.SELECT_TITLE_ALL.getSqlStatement();
        List<String> titleNames = new ArrayList<>();
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            rs = statement.executeQuery();
            while (rs.next()) {
                String titleName = rs.getString("title_name").replaceAll("§[a-zA-Z0-9]", "");
                titleNames.add(titleName);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            TheNorthTitle.getInstance().getLogger().warning("查询所有称号失败");
        } finally {
            try {
                if (statement != null) statement.close();
                if (rs != null) rs.close();
                ConnectionPool.returnSource(connection);
            } catch (SQLException e) {
            }
        }
        return titleNames;
    }

    public static String selectId(String name) {
        PreparedStatement statement = null;
        Connection connection = null;
        ResultSet rs = null;
        String sql = SqlEnum.SELECT_NAME.getSqlStatement();
        String id = "妹有前置";
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            rs = statement.executeQuery();
            while (rs.next()) id = rs.getString("id");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (rs != null) rs.close();
                ConnectionPool.returnSource(connection);
            } catch (SQLException e) {
                // no code
            }
        }
        return id;
    }

    public static int insertNewTitle(TheNorthTitleData titleData) {
        PreparedStatement statement = null;
        Connection connection = null;
        String sql = SqlEnum.NEW_TITLE.getSqlStatement();
        int insert = 0;
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, titleData.getTitleName());
            statement.setDouble(2, titleData.getTitlePrice());
            statement.setString(3, titleData.getTitleRelyOn());
            statement.setDouble(4, titleData.getTitleShopDiscount());
            statement.setDouble(5, titleData.getTitleRewardMul());
            statement.setString(6, titleData.getCreateBy());
            statement.setString(7, titleData.getCreateTime());
            statement.setString(8, titleData.getUpdateBy());
            statement.setString(9, titleData.getUpdateTime());
            statement.setString(10, titleData.getBelongTo());
            insert = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                ConnectionPool.returnSource(connection);
            } catch (SQLException e) {
            }
        }
        return insert;
    }

    public static TheNorthTitleData selectTitleInfo(Integer id) {
        PreparedStatement statement = null;
        Connection connection = null;
        ResultSet rs = null;
        TheNorthTitleData titleData = new TheNorthTitleData();
        String sql = SqlEnum.SELECT_TITLE_INFO.getSqlStatement();
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            rs = statement.executeQuery();
            while (rs.next()) {
                titleData.setId(id);
                titleData.setTitleName(rs.getString("title_name"));
                titleData.setTitlePrice(rs.getDouble("title_price"));
                titleData.setTitleRelyOn(rs.getString("title_rely_on"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
            TheNorthTitle.getInstance().getLogger().warning("获取称号信息错误");
        } finally {
            try {
                if (statement != null) statement.close();
                if (rs != null) rs.close();
                ConnectionPool.returnSource(connection);
            } catch (SQLException e) {
            }
        }
        return titleData;
    }

    public static int giveTitle(TheNorthTitlePlayer theNorthTitlePlayer) {
        PreparedStatement statement = null;
        Connection connection = null;
        String sql = SqlEnum.GIVE_TITLE.getSqlStatement();
        int count = 0;
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, theNorthTitlePlayer.getPlayerUuid());
            statement.setString(2, theNorthTitlePlayer.getPlayerName());
            statement.setInt(3, theNorthTitlePlayer.getTitleId());
            statement.setString(4, theNorthTitlePlayer.getAchieveTime());
            count = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                ConnectionPool.returnSource(connection);
            } catch (SQLException e) {
            }
        }
        return count;
    }

    public static void initUserTitle(Player player) {
        PreparedStatement title = null;
        PreparedStatement user = null;
        Connection connection = null;
        String titleSql = SqlEnum.INSERT_USER_DEFAULT.getSqlStatement();
        String userSql = SqlEnum.INSERT_USER_NOW.getSqlStatement();
        try {
            connection = ConnectionPool.getConnection();
            title = connection.prepareStatement(titleSql);
            title.setString(1, player.getUniqueId().toString());
            title.setString(2, player.getName());
            title.setInt(3, 2);
            title.setString(4, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
            title.setString(5, player.getName());
            title.execute();
            user = connection.prepareStatement(userSql);
            user.setString(1, player.getName());
            user.setString(2, player.getUniqueId().toString());
            user.setInt(3, 2);
            user.setString(4, player.getName());
            user.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (title != null) title.close();
                if (user != null) user.close();
                ConnectionPool.returnSource(connection);
            } catch (SQLException e) {
            }
        }
    }

    public static List<PlayerTitle> queryPlayerTitleByPage(Player player, Integer start, Integer pageSize, Integer type) {
        String name = player.getName();
        PreparedStatement statement = null;
        Connection connection = null;
        ResultSet rs = null;
        String sql = type == 1 ? SqlEnum.SELECT_PLAYER_TITLE.getSqlStatement() : SqlEnum.SELECT_PLAYER_NOT_BOUGHT_TITLE.getSqlStatement();
        LinkedList<PlayerTitle> playerTitles = new LinkedList<>();
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            statement.setInt(2, start);
            statement.setInt(3, pageSize);
            rs = statement.executeQuery();
            while (rs.next()) {

                PlayerTitle playerTitle = new PlayerTitle();
                playerTitle.setId(rs.getInt("id"))
                        .setTitleName(rs.getString("title_name"))
                        .setPrice("§b价值:" + rs.getString("title_price") + " §e金币")
                        .setTitlePre("§a前置称号: " + rs.getString("title_rely_on"))
                        .setTitleDisCount("§6系统商店金币折扣: §c" + rs.getBigDecimal("title_shop_discount").multiply(new BigDecimal(100)) + "%")
                        .setTitleRewardMul("§e每日奖励倍率: §c" + rs.getString("title_reward_mul"));
                playerTitles.add(playerTitle);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (rs != null) rs.close();
                ConnectionPool.returnSource(connection);
            } catch (SQLException e) {
            }
        }
        return playerTitles;
    }

    public static boolean isVip(Player player) {
        final String playerName = player.getName();
        int isVip = 0;
        PreparedStatement statement = null;
        Connection connection = null;
        ResultSet rs = null;
        String sql = SqlEnum.SELECT_IS_VIP.getSqlStatement();
        try {
            connection = ConnectionPool.getConnection();
            statement = connection.prepareStatement(sql);
            statement.setString(1, playerName);
            rs = statement.executeQuery();
            while (rs.next()) isVip = rs.getInt(1);
            return isVip > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (statement != null) statement.close();
                if (rs != null) rs.close();
                ConnectionPool.returnSource(connection);
            } catch (SQLException e) {
                // none code
            }
        }
        return false;
    }

}
